<?php
class moveto_booking{    
    
	
	/* Object property booking Identity */
	public $id;	
		
	/* Object property order Identity */
	public $order_id;	
	
	/* Object property client Identity */
	public $client_id;	
	
	/* Object property service Identity */
	public $service_id;

	/* Object property source city Identity */	
	public $source_city;	
	
	/* Object property booking destination city */
	public $destination_city;	
	
	/* Object property booking datetime */
	public $booking_date;
	
	/* Object property booking quote price */
	public $quote_price;		
	
	/* Object property quote detail */
	public $quote_detail;
	
	/* Object property service info string */
	public $service_info;
	
	/* Object property articles info string */
	public $articles_info;
	
	/* Object property loading info string */
	public $loading_info;
	
	/* Object property unloading info string */
	public $unloading_info;
	
	/* Object property additional info string */
	public $additional_info;
	
	/* Object property user info string */
	public $user_info;
	
	/* Object property booking status */
	public $booking_status;
	
	/* Object property reminder */
	public $reminder;
		
	/* Object property last modify date*/
	public $lastmodify;
	
	/* Object property last order id*/
	public $last_order_id;
	
	/* Object property last order id*/
	public $notification;

	public $booking_id;
	public $user_login;
	public $user_nicename;
	public $user_email;
	public $reg_date;
	public $display_name;
	public $bookingId;
	public $via_city;
	public $payment_method;
	public $unloading_data;
	public $loading_data;
	public $service_cubicfeets;
	public $manpower_info;
	public $insurance_rate;
	public $cubic_feets_volume;
	public $move_size;
	public $cubic_feets_article;
	public $transaction_id;


	
	 /**
     * create moveto bookings table
     */ 
	function create_table() {
	global $wpdb;

	$table_name = $wpdb->prefix .'mp_bookings';
	
	if( $wpdb->get_var( "show tables like '{$table_name}'" ) != $table_name ) {		
	
	$sql = "CREATE TABLE IF NOT EXISTS ".$table_name." (
			  `id` int(11) NOT NULL AUTO_INCREMENT,
			  `order_id` int(11) NOT NULL,
			  `client_id` int(11) NOT NULL,
			  `source_city` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
			  `via_city` varchar(500) COLLATE utf8_unicode_ci NOT NULL, 
			  `destination_city` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
			  `move_size` varchar(100) COLLATE utf8_unicode_ci NOT NULL,	
			  `booking_date` varchar(18) COLLATE utf8_unicode_ci NOT NULL,			  
			  `date_period` varchar(50) COLLATE utf8_unicode_ci NOT NULL,			  
			  `quote_price` text COLLATE utf8_unicode_ci NOT NULL,
			  `quote_detail` longtext COLLATE utf8_unicode_ci NOT NULL,
			  `service_info` longtext COLLATE utf8_unicode_ci NOT NULL,
			  `loading_info` longtext COLLATE utf8_unicode_ci NOT NULL,
			  `unloading_info` longtext COLLATE utf8_unicode_ci NOT NULL,
			  `additional_info` longtext COLLATE utf8_unicode_ci NOT NULL,
			  `manpower_info` varchar(18) COLLATE utf8_unicode_ci NOT NULL,
			  `user_info` longtext COLLATE utf8_unicode_ci NOT NULL,
			  `booking_status` enum('P','D') COLLATE utf8_unicode_ci NOT NULL COMMENT 'A=active, D=Done',
				`payment_method` varchar(18) COLLATE utf8_unicode_ci NOT NULL,
				`transaction_id` text COLLATE utf8_unicode_ci NOT NULL,
			  `reminder` enum('0','1') COLLATE utf8_unicode_ci NOT NULL DEFAULT '0' COMMENT '0=Email Not Sent,1=Email Sent',
			  `notification` enum('0','1') COLLATE utf8_unicode_ci NOT NULL DEFAULT '0' COMMENT '0=Unread,1=Read',
			  `lastmodify`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
			  `insurance_rate` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
			  `cubic_feets_article` longtext COLLATE utf8_unicode_ci NOT NULL,
			  `cubic_feets_volume` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
			  `distances` varchar(10) COLLATE utf8_unicode_ci NOT NULL,

			  PRIMARY KEY (`id`)
			) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;";
	
	
	dbDelta($sql);     
			}
	} 

	/*function add_booking(){
		 global $wpdb;		
			
		$result = $wpdb->query("INSERT INTO ".$wpdb->prefix."mp_bookings (`id`,`order_id`, `client_id`, `service_id`, `source_city`, `destination_city`, `booking_date`, `quote_price`, `quote_detail`, `service_info`, `articles_info`, `loading_info`, `unloading_info`, `additional_info`, `user_info`, `booking_status`, `reminder`, `notification`, `lastmodify`,`cabs_info`) VALUES ('','".$this->order_id."','".$this->client_id."','".$this->service_id."','".$this->source_city."','".$this->destination_city."','".$this->booking_date."','".$this->quote_price."','".$this->quote_detail."','".$this->service_info."','".$this->articles_info."','".$this->loading_info."','".$this->unloading_info."','".$this->additional_info."','".$this->user_info."','".$this->booking_status."','".$this->reminder."','".$this->notification."','".$this->lastmodify."','".$this->cabs_info."')"); 		
			
			return $result; 
	 }*/
	 
	/** Add Booking Records In the Booking Table [New Code For New MoveTo]-START**/

	 function add_bookings(){
	    global $wpdb;
		
		$result = $wpdb->query("UPDATE ".$wpdb->prefix."mp_bookings SET `quote_price` = '".$this->quote_price."' , `quote_detail` = '".$this->quote_detail."' , `service_info` = '".$this->service_info."' , `loading_info` = '".$this->loading_data."' ,  `unloading_info` = '".$this->unloading_data."' , `additional_info` = '".$this->additional_info."' ,`manpower_info` = '".$this->manpower_info."' , `payment_method` = '".$this->payment_method."' , `transaction_id` = '".$this->transaction_id."', `insurance_rate` = '".$this->insurance_rate."', `cubic_feets_article` = '".$this->service_cubicfeets."',`cubic_feets_volume` = '".$this->cubic_feets_volume."' WHERE `order_id` = '".$this->booking_id."'");
		
		return $result;	
	 }

	function get_booking(){
		 global $wpdb;		

		$data = $wpdb->get_results("SELECT * FROM ".$wpdb->prefix."mp_bookings as wmb LEFT JOIN ".$wpdb->prefix."mp_order_client_info as wmoci ON wmb.order_id = wmoci.order_id WHERE wmb.order_id = '".$this->bookingId."'");
			
		return $data;
				
	 }

	 function add_wp_user_data(){
		 global $wpdb;		

		$result = $wpdb->query("INSERT INTO ".$wpdb->prefix."users (`id`,`user_login`, `user_nicename`, `user_email`, `user_registered`, `display_name`) VALUES ('','".$this->user_login."','".$this->user_nicename."','".$this->user_email."','".$this->reg_date."','".$this->display_name."')"); 		
			
		return $result; 
	 }
	 /** Add Booking Records In the Booking Table [New Code For New MoveTo]-END**/
	

	  /**
     * Read all upcoming (future appointments) For Reminder Email & SMS Notification
     *
     * @return records as object
     */
	function read_all_upcoming_bookings_reminder_notification(){      	
			global $wpdb;
			
						
			$result = $wpdb->get_results("SELECT *  FROM  ".$wpdb->prefix."mp_bookings  WHERE booking_date > '".date_i18n('Y-m-d')."' and  reminder='0' and booking_status='P'"); 		
		
			return $result;   
	} 
	 
	 
	 
	 /**
     * Read all upcoming (future appointments)
     *
     * @return records as object
     */
	function read_all_upcoming_bookings(){      	
		global $wpdb;
		$filterstring = '';
			
		$result = $wpdb->get_results("SELECT *  FROM  ".$wpdb->prefix."mp_bookings  WHERE booking_date >= '".date_i18n('Y-m-d')."'"); 		
	
		return $result;   
	} 

	 	

	/**
     * Read one record of booking
     */
	function readOne_by_booking_id(){
		global $wpdb;
		$result = $wpdb->get_results("SELECT	* FROM  ".$wpdb->prefix."mp_bookings 	WHERE id =".$this->id."	LIMIT	0,1");
				
		foreach($result as $row){			
		  $this->id = $row->id;			
		  $this->order_id = $row->order_id;			
		  $this->client_id = $row->client_id;				
		 /*  $this->service_id = $row->service_id;		 */	
		  $this->source_city = $row->source_city;	
		  $this->via_city = $row->via_city;			
		  $this->destination_city = $row->destination_city;	
		  $this->date_period = $row->date_period;	
		  $this->move_size = $row->move_size;			
		  $this->booking_date = $row->booking_date;			
		  $this->quote_price = $row->quote_price;		
		  $this->quote_detail = $row->quote_detail;		
		  $this->service_info = $row->service_info;	
		  $this->loading_info = $row->loading_info;		
		  $this->unloading_info = $row->unloading_info;		
		  $this->additional_info = $row->additional_info;
		  $this->user_info = $row->user_info;
		  $this->booking_status = $row->booking_status;
		  $this->reminder = $row->reminder;
		  $this->notification = $row->notification;
		  $this->lastmodify = $row->lastmodify;
		  $this->payment_method = $row->payment_method;
		  $this->insurance_rate = $row->insurance_rate;
		  $this->cubic_feets_article = $row->cubic_feets_article;
		  $this->cubic_feets_volume = $row->cubic_feets_volume;
		 }
	}
	function readOne_by_order_id_from_order_client(){
	
		global $wpdb;
		$stmt = $wpdb->get_results("SELECT	* FROM   ".$wpdb->prefix."mp_order_client_info	WHERE order_id =".$this->order_id."
		LIMIT	
		0,1");				 	
		foreach($stmt as $row){			
		  $this->order_id = $row->order_id;			
		  $this->client_name = $row->client_name;			
		  $this->client_email = $row->client_email;			
		  $this->client_phone = $row->client_phone;			
		  $this->client_personal_info = $row->client_personal_info;			
		 }
		
	}	 
	/**
     * Read one record of booking by order ID
     */
	function readOne_by_booking_order_id(){
			global $wpdb;
			$result = $wpdb->get_results("SELECT	* FROM  ".$wpdb->prefix."mp_bookings  WHERE order_id =".$this->order_id." LIMIT 0,1");

			foreach($result as $row){	
			  $this->id = $row->id;			
			  $this->order_id = $row->order_id;			
			  $this->client_id = $row->client_id;				
			  $this->service_id = $row->service_id;			
			  $this->source_city = $row->source_city;	
			  $this->via_city = $row->via_city;			
			  $this->destination_city = $row->destination_city;	
			  $this->move_size = $row->move_size;			
			  $this->booking_date = $row->booking_date;			
			  $this->quote_price = $row->quote_price;		
			  $this->quote_detail = $row->quote_detail;		
			  $this->service_info = $row->service_info;	
			  $this->loading_info = $row->loading_info;		
			  $this->unloading_info = $row->unloading_info;		
			  $this->additional_info = $row->additional_info;
			  $this->user_info = $row->user_info;
			  $this->booking_status = $row->booking_status;
			  $this->reminder = $row->reminder;
			  $this->notification = $row->notification;
			  $this->lastmodify = $row->lastmodify;
			}
	}

	/**
     * Read one record of booking by order ID
     */
	function read_client_recent_checkout_info(){
			global $wpdb;
			$result = $wpdb->get_results("SELECT user_info FROM  ".$wpdb->prefix."mp_bookings  WHERE client_id =".$this->client_id." LIMIT	0,1");

			if(sizeof((array)$result)>0){
				$this->user_info = $result[0]->user_info;
			}else{
				$this->user_info = '';
			}					
		}

	/**
     * Fetch last order ID
     */
	function get_last_order_id(){
		global $wpdb;
		$result = $wpdb->get_results("SELECT	order_id	FROM		
		".$wpdb->prefix."mp_bookings 
		order by order_id DESC	LIMIT 0,1");
	 
		foreach($result as $row){
			$this->last_order_id = $row->order_id;
		}
	}

	function get_last_wp_user_id(){
		global $wpdb;
		//echo "SELECT id FROM ".$wpdb->prefix."users order by id DESC	LIMIT 0,1";
		$result = $wpdb->get_results("SELECT id FROM ".$wpdb->prefix."users order by id DESC LIMIT 0,1");
		foreach($result as $row){
			$this->last_id = $row->id;
		}
	}


	/**
     * Fetch orders by client ID
	 * @param $result - db record object
    */
	function get_order_ids_by_client_id(){
		global $wpdb;
		$result = $wpdb->get_results("SELECT	order_id FROM	".$wpdb->prefix."mp_bookings 
		Where client_id=".$this->client_id);
		return $result;
	}


	/**
     * Fetch all bookings by client ID
    */
	function get_client_all_bookings_by_client_id(){
		global $wpdb;
		$result = $wpdb->get_results("SELECT	* FROM	".$wpdb->prefix."mp_bookings 
		Where client_id=".$this->client_id);
	
		return $result;
	}


	/**
     * Fetch all bookings
	 * @param $startdate - Startdate
	 * @param $enddate - Enddate
	 * @return $result - db records object
    */
	function readAll($startdate='',$enddate='',$service_id='',$reqpage=''){
				global $wpdb;
				$filterquery = '';
				if($startdate!='' && $enddate!=''){
					$filterquery .= " where booking_date >= '$startdate' AND booking_date <= '$enddate'";
				}
				/* if($service_id!=''){
					if($filterquery==''){
					$filterquery .= " where service_id='$service_id'";	
					}else{
					$filterquery .= " and service_id='$service_id'";
					}
				} */
			/* echo "SELECT * FROM ".$wpdb->prefix."mp_bookings $filterquery order by lastmodify desc";
			die; */
			$result = $wpdb->get_results("SELECT * FROM ".$wpdb->prefix."mp_bookings $filterquery order by lastmodify desc");
					
			return $result;
		
	}
	
	/**
     * Fetch all bookings
	 * @param $startdate - Startdate
	 * @param $enddate - Enddate
	 * @return $result - db records object
    */
	function readAll_listing_bookings(){
			global $wpdb;
			
			$result = $wpdb->get_results("SELECT * FROM ".$wpdb->prefix."mp_bookings order by lastmodify desc");
					
			return $result;
		
	}
	/*function get_booking(){
				global $wpdb;
				
				$result = $wpdb->get_results("SELECT * FROM ".$wpdb->prefix."mp_bookings WHERE `id` = "$this->bookingId);
						
				return $result;
			
		}*/

	/**
     * Get client orders with booking status
	 * @return $result - db records object
    */
	function get_distinct_bookings_of_client(){
		global $wpdb;
		
		$result = $wpdb->get_results("SELECT id,client_id,order_id,source_city,destination_city,booking_date,booking_status FROM  ".$wpdb->prefix."mp_bookings  where client_id=".$this->client_id." GROUP BY order_id ORDER BY lastmodify DESC" );

		return $result;
	}

	
	/**
     * Fetch client bookings by Order id
	 * @return $result - db records object
    */
	function get_client_bookings_by_order_id(){
			global $wpdb;
			$result = $wpdb->get_results("SELECT * FROM  ".$wpdb->prefix."mp_bookings 
			Where client_id=".$this->client_id." AND order_id=".$this->order_id );
			return $result;
		}
		

	
	/**
     * Update booking status by booking ID
	 * @return $result - db records object
    */
	 function update_booking_status(){
		global $wpdb;				
		$result = $wpdb->get_results("UPDATE  ".$wpdb->prefix."mp_bookings 
		SET booking_status='".$this->booking_status."',lastmodify='".date_i18n('Y-m-d H:i:s')."' WHERE id=".$this->id );		
		return $result;
	} 
function update_booking_status_by_id(){
		global $wpdb;

		$result = $wpdb->get_results("UPDATE  ".$wpdb->prefix."mp_bookings 
		SET via_city='".$this->via_city."'destination_city='".$this->destination_city."',unloading_info='".$this->unloading_info."', loading_info='".$this->loading_info."',booking_date='".$this->booking_date."',source_city='".$this->source_city."',lastmodify='".date_i18n('Y-m-d H:i:s')."' WHERE id=".$this->id );		
		return $result;
	}
	/**
	 * Get all bookings by Order ID
	 * @return $result - db records object
	*/
	 function get_all_bookings_by_order_id(){
				global $wpdb;
				$result = $wpdb->get_results("SELECT * FROM  ".$wpdb->prefix."mp_bookings 
				Where order_id=".$this->order_id );

				return $result;
		}
	/**
	 * Get all bookings by Booking ID
	 * @return $result - db records object
	*/
	function get_all_bookings_by_b_id(){
		global $wpdb;
		$result = $wpdb->get_results("SELECT * FROM  ".$wpdb->prefix."mp_bookings 
		Where id=".$this->id );

		return $result;
	}
	
	/**
	 * Get all bookings by Order ID
	 * @return $result - db records object
	*/
	 function count_order_bookings(){
				global $wpdb;
				$result = $wpdb->get_results("SELECT count(id) as bookings FROM  ".$wpdb->prefix."mp_bookings 
				Where order_id=".$this->order_id );
				return $result[0]->bookings;
		}
	
	

	 /**
	 * Fetch all past booking appointments 
	 * @return $result - db records object
	*/
	 function read_all_past_bookings(){      	
			global $wpdb;	
			$result = $wpdb->get_results("SELECT	
			*  FROM  ".$wpdb->prefix."mp_bookings  WHERE
			booking_date < '".date_i18n('Y-m-d')."'	
			"); 

		return $result;   
		}
		
		/**
		 * Fetch action pending bookings form past dates
		 * @return $result - db records object
		*/
		function read_all_past_bookings_action_pending(){      	
			global $wpdb;	
				$result = $wpdb->get_results("SELECT	
				*  FROM  ".$wpdb->prefix."mp_bookings  WHERE
				booking_date < '".date_i18n('Y-m-d')."' AND booking_status='P'"); 
				return $result;   
			}
		
		/**
		 * Fetch action pending bookings for future dates
		 * @return $result - db records object
		*/
		function read_all_upcomming_bookings_action_pending(){      	
				global $wpdb;	
				$result = $wpdb->get_results("SELECT	
				*  FROM  ".$wpdb->prefix."mp_bookings  WHERE
				booking_date > '".date_i18n('Y-m-d')."' AND booking_status='P'"); 
				return $result;   
		}
		
		
		/**
		 * Send Qupte for booking
		 * @return $result - db records object
		*/
		
	
		function send_quote(){
				global $wpdb;
				
				$queryString = "UPDATE  ".$wpdb->prefix."mp_bookings  SET quote_price='".$this->quote_price."',quote_detail='".$this->quote_detail."' WHERE id=".$this->id;

				$result= $wpdb->query($queryString);

				return $result;
		}
		
		

		/**
		 * Update appointment reminder email Status
		 * @return $result - db records object
		*/
		function update_booking_reminder_buffer_status(){
				global $wpdb;
				$queryString = "UPDATE  ".$wpdb->prefix."mp_bookings 
				SET reminder='".$this->reminder."' WHERE id=".$this->id;

				$result= $wpdb->query($queryString);

				return $result;
		}
		
		function delete_booking() {			
			global $wpdb;						
			$result = $wpdb->query("delete from ".$wpdb->prefix."mp_bookings  WHERE id=".$this->id); 		
		}					
		
		function get_guest_users_booking_by_order_id() {
		
				global $wpdb;		
				$result = $wpdb->get_results("SELECT * FROM  ".$wpdb->prefix."mp_bookings 	where order_id=".$this->order_id );		
			return $result;		
			
		}				
		
		function delete_users_booking_by_user_id() {	
		
				global $wpdb;		
				$result = $wpdb->get_results("delete FROM  ".$wpdb->prefix."mp_bookings Where client_id=".$this->client_id );						
			return $result;			
			
		}
		
	   function delete_users_booking_by_order_id(){
			global $wpdb;
			$result = $wpdb->query("delete from ".$wpdb->prefix."mp_bookings where order_id=".$this->order_id);
			return $result;
		}
		
		
		function get_register_client_last_order_id(){
			global $wpdb;
		
			$result = $wpdb->get_results("select order_id from ".$wpdb->prefix."mp_bookings where client_id=".$this->client_id." order by id desc limit 0,1");
			
			$this->order_id = $result[0]->order_id;			
		
		}
		
		function get_today_booking_and_earning(){
			global $wpdb;
		
			$result = $wpdb->get_results("select count(b.id) as bookings from ".$wpdb->prefix."mp_bookings as b where b.lastmodify like '".date_i18n('Y-m-d')."%' ");
			
			return $result;
		}
		
		function get_week_booking_and_earning($weekstartdate,$weekenddatedate){
			global $wpdb;
			
			$result = $wpdb->get_results("select count(b.id) as bookings from ".$wpdb->prefix."mp_bookings as b where b.lastmodify >='".$weekstartdate."' and b.lastmodify <='".$weekenddatedate."'");
			
			return $result;
		}
		
		function get_year_booking_and_earning(){
			global $wpdb;
			
			$result = $wpdb->get_results("select count(b.id) as bookings from ".$wpdb->prefix."mp_bookings as b where b.lastmodify  like'".date_i18n('Y-')."%'");
			
			return $result;
		}
		
		function today_upcomming_appointments(){
			global $wpdb;
			$result = $wpdb->get_results("select * from ".$wpdb->prefix."mp_bookings where booking_date >='".date_i18n('Y-m-d')."' and booking_status='D'");
			
			return $result;
		}
		
		function readall_bookings_by_service_id(){
			global $wpdb;
			$result = $wpdb->get_results("select count(id) as bookings from ".$wpdb->prefix."mp_bookings where service_id ='".$this->service_id."'");
			
			return $result[0]->bookings;	
		}
		
		function readall_bookings_by_provider_id(){
			global $wpdb;
			$result = $wpdb->get_results("select count(id) as bookings from ".$wpdb->prefix."mp_bookings where provider_id ='0");
			
			return $result[0]->bookings;	
		}
		
		function get_booking_by_latest_activity(){
			global $wpdb;
			
			$result = $wpdb->get_results("select * from ".$wpdb->prefix."mp_bookings where `booking_status`='P' order by lastmodify desc limit 0,10");
			
			return $result;
		}
	
		function get_compalted_quickaction_bookings(){
			global $wpdb;
			/* echo "select * from ".$wpdb->prefix."mp_bookings where `booking_status`='D' order by booking_date desc"; */
			$result = $wpdb->get_results("select * from ".$wpdb->prefix."mp_bookings where `booking_status`='D' order by booking_date desc");
			
			return $result;
		}
	
		
		function get_notifications_count(){
			global $wpdb;			
			
				$result = $wpdb->get_results("select count(id) as notification from ".$wpdb->prefix."mp_bookings where notification='0'");
						
			return $result[0]->notification;		
		}
		
		function get_notifications_bookings(){
			global $wpdb;			
			$result = $wpdb->get_results("select * from ".$wpdb->prefix."mp_bookings where notification='0' order by lastmodify desc");			
			return $result;		
		}
		
		function remove_notifications_bookings(){
			global $wpdb;	
			if($this->id=='All'){				
				$result = $wpdb->query("update ".$wpdb->prefix."mp_bookings set notification='1'");
			}else{				
				$result = $wpdb->query("update ".$wpdb->prefix."mp_bookings set notification='1' where id='".$this->id."'");		
			}			
			return $result;		
		}
		function get_login_customer_book(){
		global $wpdb;		
		
			$data = $wpdb->get_results("SELECT * FROM ".$wpdb->prefix."users WHERE user_email = '".$this->email."' && user_pass='".$this->cpassword."'");
		return $data;
	}
	function readAll_listing_bookings_front(){
			global $wpdb;
			
			$result = $wpdb->get_results("SELECT * FROM ".$wpdb->prefix."mp_bookings WHERE `client_id` = ".$this->client_id." order by lastmodify desc");			
			return $result;
		
	}
	function update_bookings(){
		 global $wpdb;		
		 
		$result = $wpdb->query("UPDATE ".$wpdb->prefix."mp_bookings SET `service_info` = '".$this->service_info."' WHERE `id` = '".$this->booking_id."'"); 			
		return $result;
			
	 }
}
?>